# !/usr/bin/env python3
import json
import re
import csv

from openpyxl import load_workbook
import yaml

my_dict = {}


def convert_to_f_string(s: str):
    return eval(f"f'''{s}'''")


if __name__ == '__main__':
    with open(r'config.yaml', 'r', encoding='utf8') as f:
        config = yaml.load(f, Loader=yaml.Loader)
    
    workbook = load_workbook(config["data_input_file"]["file_path"])
    sheet_names = workbook.sheetnames
    sheet_name = list(filter(lambda s: config["data_input_file"]["target_sheet_key_word"] in s, sheet_names))[0]
    sheet = workbook[sheet_name]
    
    for row in sheet.iter_rows(min_row=2, min_col=2, max_col=10, values_only=True):
        department = row[0].strip()
        project = row[1].strip()
        male = row[2]
        female = row[3]
        total = row[4]
        
        if project == "个检报告":
            my_dict["男"] = male
            my_dict["女"] = female
        if project == "男外科":
            my_dict["男外科"] = total
        if project == "女外科":
            my_dict["女外科"] = total
        if project == "外眼":
            my_dict["外眼"] = total
        
        m = re.findall(r"([*/])\s*(\d+)", project)
        if m:
            tmp = eval(f"{total}{m[0][0]}{m[0][1]}")
        else:
            tmp = total
        
        if department not in my_dict:
            my_dict[department] = tmp
        else:
            my_dict[department] += tmp
    
    print(json.dumps(my_dict, indent=2, ensure_ascii=False))
    for key, value in my_dict.items():
        globals()[key] = value
    
    # 打开 CSV 文件
    with open(config["csv_file"], mode='r', encoding='utf-8') as file:
        reader = csv.reader(file)
        csv_data = {(row[0], row[1]): row[2] for row in reader}
    
    workbook = load_workbook(config["output_template"]["file_path"])
    sheet_names = workbook.sheetnames
    sheet_name = list(filter(lambda s: config["output_template"]["target_sheet_key_word"] in s, sheet_names))[0]
    sheet = workbook[sheet_name]
    
    area = None
    for row in sheet.iter_rows(min_col=2, max_col=4, values_only=False):
        area = row[0].value if row[0].value else area
        department = row[1].value if row[1].value else ''
        detail = row[2].value.rstrip() if row[2].value else ''
        
        if (area, department,) in csv_data:
            row[2].value = detail + " " + convert_to_f_string(csv_data[area, department])
    
    # sheet["B1"].value = convert_to_f_string(sheet["B1"].value)
    sheet["B1"].value = sheet["B1"].value.replace("女", f' {my_dict["女"]}女').replace("男", f' {my_dict["男"]}男')
    workbook.save(config["output_file"])
